package dao;

import dto.UserDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class UserDAO {

  public static final String Tab =
    "create table senzu_user (" +
    "id serial," + //pk
    "gid int," + // 0: root, 1: regular, 2: médico
    "id_medico int," + //fk?
    "username text," + //minúsculas, letras y números
    "password text" +
    ");"
  ;

  public static final String Tuples =
    // root
    "insert into senzu_user (gid, id_medico, username, password) values" +
    "(0, -1, 'root', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50');" ; // +

  /*
    "insert into senzu_user (gid, id_medico, username, password) values" +
    // médicos
    "(2, 1, 'bones', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 2, 'elche', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 3, 'carrillo', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 4, 'docock', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 5, 'rené', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 6, 'nick', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 7, 'pirovano', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 8, 'eldoctor', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 9, 'alicia', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 10, 'garrahan', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 11, 'manzur', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(2, 12, 'ginés', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    // secretarias
    "(1, -1, 'emmanuelle', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(1, -1, 'juliana', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(1, -1, 'gabriela', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50')," +
    "(1, -1, 'daniela', '01e0b0f1040a7120cdabda0d0bf95fa6c0010a2f3b4464dcf4e33a4e259e1402651f341d2dfecc4f0518ef3c5cf2082f8db1925df5d9e2779dad398e9cd02b50');"
  ;
  */

  private static UserDAO dao = null;
  private final Connection c;

//  Devuelve una unica instancia de la Clase, aplicando patron de disenio Singleton
  public static UserDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new UserDAO();
    }
    return dao;
  }

  private UserDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("senzu_user");
  }

  public ArrayList<UserDTO> All() throws SQLException {
    return getUserDTOs("select * from senzu_user where gid != 0 order by username;");
  }

  //SE devuelve Objeto, para luego comparar si permanece null la variable o no cuando se invoca este metodo.
  public UserDTO Search(String username, String password) throws SQLException {
    String query = String.format(
      "select * from senzu_user where username = '%s' and password = '%s';",
      username, password
    );
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    UserDTO dto = null;
    if (r.next()) {
      dto = new UserDTO(
        r.getInt("id"),
        r.getInt("gid"),
        r.getInt("id_medico"),
        r.getString("username"),
        r.getString("password")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  public ArrayList<UserDTO> SearchByUsername(String s) throws SQLException {
    return getUserDTOs(queryByUsername(s));
  }

  public ArrayList<UserDTO> SearchByFullUsername(String s) throws SQLException {
    return getUserDTOs(queryByFullUsername(s));
  }

  public ArrayList<UserDTO> SearchByMedicoID(int idMedico) throws SQLException {
    return getUserDTOs(queryByMedicoID(idMedico));
  }

  private String queryByUsername(String username) throws SQLException {
    String query = String.format(
      "select * from senzu_user where gid != 0 and username ~* '^%s' order by username;",
      username
    );
    return query;
  }

  private String queryByFullUsername(String username) throws SQLException {
    String query = String.format(
      "select * from senzu_user where gid != 0 and username = '%s' order by username;",
      username
    );
    return query;
  }

  private String queryByMedicoID(int idMedico) throws SQLException {
    String query = String.format(
      "select * from senzu_user where id_medico = %d;", idMedico
    );
    return query;
  }

  private ArrayList<UserDTO> getUserDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<UserDTO> users = new ArrayList<>();
    while (r.next()) {
      users.add(
        new UserDTO(
          r.getInt("id"),
          r.getInt("gid"),
          r.getInt("id_medico"),
          r.getString("username"),
          r.getString("password")
        )
      );
    }
    r.close();
    s.close();
    return users;
  }

  public UserDTO UserDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from senzu_user where id = " + id
    );
    UserDTO dto = null;
    if (r.next()) {
      dto = new UserDTO(
        r.getInt("id"),
        r.getInt("gid"),
        r.getInt("id_medico"),
        r.getString("username"),
        r.getString("password")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  public int Save(UserDTO u) throws SQLException {
    String query = String.format(
      "insert into senzu_user values (default, %d, %d, '%s', '%s');",
      u.getGid(), u.getIdMedico(), u.getUsername(), u.getPassword()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void Update(UserDTO u) throws SQLException {
    String query = String.format(
      "update senzu_user set username = '%s' where id = %d;",
      u.getUsername(), u.getId()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }

  public void EditString(int id, String attr, String str) throws SQLException {
    String query = String.format(
      "update senzu_user set %s = '%s' where id = %d;",
      attr, str, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }

  /*
  // not needed?
  public UserDTO EditInt(int id, String attr, int n) throws SQLException {
    String query = String.format(
      "update senzu_user set %s = %d where id = %d;",
      attr, n, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return UserDTO(id);
  }
  */

}
